CREATE OR REPLACE FUNCTION "FUN_GETREGIONDEPTMAPPEDDEPTID"
(
  pRegionDeptId NUMBER
)
  RETURN numberTable_type
IS
/******************************************************************************
   NAME:      Fun_GetDeptIdByRegionDept
   PURPOSE:   获取区域部门映射的部门ID列表

   PARAMETERS:
    pRegionDeptId:报表部门


   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1        2012-10-23  MXPiao           Created

   NOTES:


******************************************************************************/
  map_index t_ms_regiondeptmapdept%ROWTYPE;
  outList numberTable_type:=numberTable_type();
BEGIN
  IF ((pRegionDeptId IS NULL) OR LENGTH(to_char(pRegionDeptId))=0) THEN
      FOR cur_row IN (
          SELECT * FROM t_ms_department
          where isactive=1 AND depttype=1
          CONNECT BY PRIOR DeptId=parentDeptId
          START WITH DeptId=1)
      LOOP
         outList.EXTEND;
         outList (outList.COUNT) := cur_row.deptid;
END LOOP;

ELSE
BEGIN
FOR map_index IN
        (SELECT * FROM t_ms_regiondeptmapdept r WHERE regiondeptid=pRegionDeptId)
      LOOP
          FOR cur_row IN (
              SELECT * FROM t_ms_department
              where isactive=1 AND depttype=1
              CONNECT BY PRIOR DeptId=parentDeptId
              START WITH DeptId=map_index.deptid)
           LOOP
              outList.EXTEND;
              outList (outList.COUNT) := cur_row.deptid;
END LOOP;
END LOOP;
END;
END IF;

RETURN outList;
end;

